package com.hotel.common.utils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库操作工具类
 * 用于执行SQL增删改查操作，支持多数据源
 */
public class DBTools {
    // 主数据库配置 (hotel_cloud)
    private static String primaryUrl = "jdbc:mysql://localhost:3306/hotel_cloud?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai";
    private static String primaryUser = "root";
    private static String primaryPassword = "123456";
    
    // 业务数据库配置 (hotel_cloud_po)
    private static String businessUrl = "jdbc:mysql://localhost:3306/hotel_cloud_po?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai";
    private static String businessUser = "root";
    private static String businessPassword = "123456";
    
    // 当前使用的数据源类型
    private static String currentDataSource = "primary";

    // 加载驱动类
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("MySQL驱动加载失败", e);
        }
    }
    
    /**
     * 设置当前使用的数据源
     * @param dataSourceType 数据源类型：primary(主数据库) 或 business(业务数据库)
     */
    public static void setDataSource(String dataSourceType) {
        if ("primary".equals(dataSourceType) || "business".equals(dataSourceType)) {
            currentDataSource = dataSourceType;
        } else {
            throw new IllegalArgumentException("不支持的数据源类型: " + dataSourceType);
        }
    }
    
    /**
     * 获取当前数据源配置
     * @return 连接配置数组 [url, user, password]
     */
    private static String[] getCurrentDataSourceConfig() {
        if ("business".equals(currentDataSource)) {
            return new String[]{businessUrl, businessUser, businessPassword};
        } else {
            return new String[]{primaryUrl, primaryUser, primaryPassword};
        }
    }
    
    /**
     * 获取数据库连接
     * @return 数据库连接
     * @throws SQLException SQL异常
     */
    private static Connection getConnection() throws SQLException {
        String[] config = getCurrentDataSourceConfig();
        return DriverManager.getConnection(config[0], config[1], config[2]);
    }

    /**
     * 通用查询方法
     * @param sql SQL语句
     * @param objs 参数数组
     * @return 查询结果列表
     */
    public static List<Map<String, String>> query(String sql, Object... objs) {
        List<Map<String, String>> list = new ArrayList<>();
        try (Connection conn = getConnection();
             PreparedStatement pps = conn.prepareStatement(sql)) {
            
            if (objs != null) {
                for (int i = 0; i < objs.length; i++) {
                    pps.setObject(i + 1, objs[i]);
                }
            }
            
            try (ResultSet resultSet = pps.executeQuery()) {
                ResultSetMetaData metaData = resultSet.getMetaData();
                List<String> columnNames = new ArrayList<>();
                for (int i = 0; i < metaData.getColumnCount(); i++) {
                    columnNames.add(metaData.getColumnLabel(i + 1));
                }

                while (resultSet.next()) {
                    Map<String, String> map = new HashMap<>();
                    for (String columnName : columnNames) {
                        map.put(columnName, resultSet.getString(columnName));
                    }
                    list.add(map);
                }
            }
        } catch (SQLException e) {
            throw new BusinessException("数据库查询失败: " + e.getMessage(), e);
        }
        return list;
    }

    /**
     * 通用更新方法（增删改）
     * @param sql SQL语句
     * @param objs 参数数组
     * @return 受影响的行数
     */
    public static int update(String sql, Object... objs) {
        try (Connection conn = getConnection();
             PreparedStatement pps = conn.prepareStatement(sql)) {
            
            if (objs != null) {
                for (int i = 0; i < objs.length; i++) {
                    pps.setObject(i + 1, objs[i]);
                }
            }
            return pps.executeUpdate();
        } catch (SQLException e) {
            throw new BusinessException("数据库更新失败: " + e.getMessage(), e);
        }
    }

    /**
     * 批量更新方法
     * @param sql SQL语句
     * @param paramsList 参数列表
     * @return 受影响的行数数组
     */
    public static int[] batchUpdate(String sql, List<Object[]> paramsList) {
        try (Connection conn = getConnection();
             PreparedStatement pps = conn.prepareStatement(sql)) {
            
            conn.setAutoCommit(false);
            for (Object[] params : paramsList) {
                for (int i = 0; i < params.length; i++) {
                    pps.setObject(i + 1, params[i]);
                }
                pps.addBatch();
            }
            int[] results = pps.executeBatch();
            conn.commit();
            return results;
        } catch (SQLException e) {
            throw new BusinessException("批量更新失败: " + e.getMessage(), e);
        }
    }

    /**
     * 获取单个值（如主键）
     * @param sql SQL语句
     * @param objs 参数数组
     * @return 单个值
     */
    public static String querySingle(String sql, Object... objs) {
        List<Map<String, String>> result = query(sql, objs);
        if (!result.isEmpty() && !result.get(0).isEmpty()) {
            return result.get(0).values().iterator().next();
        }
        return null;
    }

    /**
     * 获取记录总数
     * @param sql SQL语句
     * @param objs 参数数组
     * @return 记录总数
     */
    public static int count(String sql, Object... objs) {
        String countSql = "SELECT COUNT(1) as count FROM (" + sql + ") as temp";
        String result = querySingle(countSql, objs);
        return result != null ? Integer.parseInt(result) : 0;
    }
    
    /**
     * 使用主数据库执行查询
     * @param sql SQL语句
     * @param objs 参数数组
     * @return 查询结果列表
     */
    public static List<Map<String, String>> queryWithPrimary(String sql, Object... objs) {
        String originalDataSource = currentDataSource;
        try {
            setDataSource("primary");
            return query(sql, objs);
        } finally {
            setDataSource(originalDataSource);
        }
    }
    
    /**
     * 使用业务数据库执行查询
     * @param sql SQL语句
     * @param objs 参数数组
     * @return 查询结果列表
     */
    public static List<Map<String, String>> queryWithBusiness(String sql, Object... objs) {
        String originalDataSource = currentDataSource;
        try {
            setDataSource("business");
            return query(sql, objs);
        } finally {
            setDataSource(originalDataSource);
        }
    }
    
    /**
     * 使用主数据库执行更新
     * @param sql SQL语句
     * @param objs 参数数组
     * @return 受影响的行数
     */
    public static int updateWithPrimary(String sql, Object... objs) {
        String originalDataSource = currentDataSource;
        try {
            setDataSource("primary");
            return update(sql, objs);
        } finally {
            setDataSource(originalDataSource);
        }
    }
    
    /**
     * 使用业务数据库执行更新
     * @param sql SQL语句
     * @param objs 参数数组
     * @return 受影响的行数
     */
    public static int updateWithBusiness(String sql, Object... objs) {
        String originalDataSource = currentDataSource;
        try {
            setDataSource("business");
            return update(sql, objs);
        } finally {
            setDataSource(originalDataSource);
        }
    }
}